RecordsAffected Property Example

This example uses the RecordsAffected property with action queries executed from a Database object and from a QueryDef object. The RecordsAffectedOutput function is required for this procedure to run.

Sub RecordsAffectedX()

   Dim dbsNorthwind As Database
   Dim qdfTemp As QueryDef
   Dim strSQLChange As String
   Dim strSQLRestore As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   With dbsNorthwind
      ' Print report of contents of the Employees 
      ' table.
      Debug.Print _
         "Number of records in Employees table: " & _
         .TableDefs!Employees.RecordCount
      RecordsAffectedOutput dbsNorthwind

      ' Define and execute an action query.
      strSQLChange = "UPDATE Employees " & _
         "SET Country = 'United States' " & _
         "WHERE Country = 'USA'"
      .Execute strSQLChange

      ' Print report of contents of the Employees 
      ' table.
      Debug.Print _
         "RecordsAffected after executing query " & _
         "from Database: " & .RecordsAffected
      RecordsAffectedOutput dbsNorthwind

      ' Define and run another action query.
      strSQLRestore = "UPDATE Employees " & _
         "SET Country = 'USA' " & _
         "WHERE Country = 'United States'"
      Set qdfTemp = .CreateQueryDef("", strSQLRestore)
      qdfTemp.Execute

      ' Print report of contents of the Employees 
      ' table.
      Debug.Print _
         "RecordsAffected after executing query " & _
         "from QueryDef: " & qdfTemp.RecordsAffected
      RecordsAffectedOutput dbsNorthwind

      .Close

   End With

End Sub

Function RecordsAffectedOutput(dbsNorthwind As Database)

   Dim rstEmployees As Recordset

   ' Open a Recordset object from the Employees table.
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   With rstEmployees
      ' Enumerate Recordset.
      .MoveFirst
      Do While Not .EOF
         Debug.Print "  " & !LastName & ", " & !Country
         .MoveNext
      Loop
      .Close
   End With

End Function